package db;

import beans.Drivers;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DriversDao extends MySQLConnectionBean {
    private MySQLConnectionBean connection;

    public DriversDao() {
        connection = new MySQLConnectionBean();
    }

    // 插入司机信息（SQL 保持兼容）
    public boolean addDriver(Drivers driver) {
        System.out.println("adddriver_DAO");
        try {
            Connection conn = connection.getConnection();
            String sql = "INSERT INTO drivers (DriverID, DriverName, PhoneNumber, Email, Remarks, Gender, DriverLicenseNumber, DriverLicenseExpirationDate, DriverLicenseImage, status, password) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

            PreparedStatement pstmt = conn.prepareStatement(sql);
            System.out.println(111);
            // 绑定参数
            pstmt.setInt(1, driver.getDriverId());
            pstmt.setString(2, driver.getDriverName());
            pstmt.setString(3, driver.getPhoneNumber());
            pstmt.setString(4, driver.getEmail());
            pstmt.setString(5, driver.getRemarks());
            pstmt.setString(6, driver.getGender());
            pstmt.setString(7, driver.getDriverLicenseNumber());
            pstmt.setDate(8, new java.sql.Date(driver.getDriverLicenseExpirationDate().getTime()));
            pstmt.setString(9, driver.getDriverLicenseImage());
            pstmt.setString(10, "空闲");
            // 确保密码不为空
            if (driver.getPassword() == null || driver.getPassword().trim().isEmpty()) {
                throw new SQLException("密码不能为空");
            }
            pstmt.setString(11, driver.getPassword());

            int rowsAffected = pstmt.executeUpdate();
            return rowsAffected > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 查询所有司机（SQL 保持兼容）
    public List<Drivers> getAllDrivers() {
        System.out.println("DAO");
        List<Drivers> drivers = new ArrayList<>();
        String sql = "SELECT * FROM drivers";

        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                Drivers driver = extractDriverFromResultSet(rs);
                drivers.add(driver);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return drivers;
    }
    public List<Drivers> getAllDriver() throws SQLException {
        List<Drivers> drivers = new ArrayList<>();
        String sql = "SELECT DriverID AS driverId, DriverName AS driverName FROM drivers"; // 确保包含必要字段

        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {

            while (rs.next()) {
                Drivers driver = new Drivers();
                driver.setDriverId(rs.getInt("DriverID"));
                driver.setDriverName(rs.getString("DriverName"));
                drivers.add(driver);
            }
        }
        return drivers;
    }

    //查询司机
    public List<Drivers> searchDrivers(
            String driverId,
            String driverName,
            String phoneNumber,
            String gender,
            String status
    ) {
        List<Drivers> drivers = new ArrayList<>();
        StringBuilder sql = new StringBuilder("SELECT * FROM drivers WHERE 1=1");
        List<Object> params = new ArrayList<>();

        // 参数处理（注意DriverID是int类型）
        if (driverId != null && !driverId.isEmpty()) {
            sql.append(" AND DriverID = ?");  // 精确查询（因ID为唯一值）
            params.add(Integer.parseInt(driverId)); // 转为int
        }
        if (driverName != null && !driverName.isEmpty()) {
            sql.append(" AND DriverName LIKE ?");
            params.add("%" + driverName + "%");
        }
        if (phoneNumber != null && !phoneNumber.isEmpty()) {
            sql.append(" AND PhoneNumber LIKE ?");
            params.add("%" + phoneNumber + "%");
        }
        if (gender != null && !gender.isEmpty()) {
            sql.append(" AND Gender = ?");
            params.add(gender);
        }
        if (status != null && !status.isEmpty()) {
            sql.append(" AND status = ?");
            params.add(status);
        }

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql.toString())) {

            // 参数设置（严格按顺序）
            for (int i = 0; i < params.size(); i++) {
                Object param = params.get(i);
                if (param instanceof Integer) {
                    pstmt.setInt(i + 1, (Integer) param);
                } else {
                    pstmt.setString(i + 1, param.toString());
                }
            }

            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Drivers driver = extractDriverFromResultSet(rs);
                drivers.add(driver);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return drivers;
    }

    private Drivers extractDriverFromResultSet(ResultSet rs) throws SQLException {
        Drivers driver = new Drivers();
        driver.setDriverId(rs.getInt("DriverId")); // 使用正确列名
        driver.setDriverName(rs.getString("DriverName"));
        driver.setPhoneNumber(rs.getString("PhoneNumber"));
        driver.setEmail(rs.getString("Email"));
        driver.setRemarks(rs.getString("Remarks"));      // 新增字段
        driver.setGender(rs.getString("Gender"));
        driver.setStatus(rs.getString("status"));
        driver.setDriverLicenseNumber(rs.getString("DriverLicenseNumber"));
        driver.setDriverLicenseExpirationDate(rs.getDate("DriverLicenseExpirationDate"));
        driver.setDriverLicenseImage(rs.getString("DriverLicenseImage"));
        driver.setPassword(rs.getString("password"));  // 添加密码字段
        return driver;
    }

    // 更新司机信息
    public boolean updateDriver(Drivers driver) throws SQLException {
        // 修改后的SQL和参数绑定：
        String sql = "UPDATE drivers SET DriverName=?, PhoneNumber=?, Email=?, Gender=?, " +
                "DriverLicenseNumber=?, DriverLicenseExpirationDate=?, DriverLicenseImage=?, Remarks=?, status=? " +
                "WHERE DriverID=?";

        try (Connection conn = connection.getConnection();
             PreparedStatement statement = conn.prepareStatement(sql)) {

            statement.setString(1, driver.getDriverName());
            statement.setString(2, driver.getPhoneNumber());
            statement.setString(3, driver.getEmail());
            statement.setString(4, driver.getGender());
            statement.setString(5, driver.getDriverLicenseNumber());

            // 处理可能为NULL的日期字段
            if (driver.getDriverLicenseExpirationDate() != null) {
                statement.setDate(6, new java.sql.Date(driver.getDriverLicenseExpirationDate().getTime()));
            } else {
                statement.setNull(6, Types.DATE);
            }

            statement.setString(7, driver.getDriverLicenseImage());
            statement.setString(8, driver.getRemarks());
            statement.setString(9, driver.getStatus());      // status 是第9个参数
            statement.setInt(10, driver.getDriverId());      // WHERE条件第10个参数

            return statement.executeUpdate() > 0;
        }
    }

    // 删除司机
    public boolean deleteDriver(int driverId) throws SQLException {
        String sql = "DELETE FROM drivers WHERE DriverID=?";

        try (Connection conn = connection.getConnection();
             PreparedStatement statement = conn.prepareStatement(sql)) {

            statement.setInt(1, driverId);
            return statement.executeUpdate() > 0;
        }
    }

    // 辅助方法：获取所有司机ID
    public List<Integer> getAllDriverIds() throws SQLException {
        List<Integer> ids = new ArrayList<>();
        String sql = "SELECT DriverID FROM drivers";

        try (Connection conn = connection.getConnection();
             PreparedStatement statement = conn.prepareStatement(sql);
             ResultSet rs = statement.executeQuery()) {

            while (rs.next()) {
                ids.add(rs.getInt("DriverID"));
            }
        }
        return ids;
    }

    // 根据ID获取司机
    public Drivers getDriverById(int driverId) throws SQLException {
        String sql = "SELECT * FROM drivers WHERE DriverID = ?";

        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, driverId);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    Drivers driver = new Drivers();
                    driver.setDriverId(rs.getInt("DriverID"));
                    driver.setDriverName(rs.getString("DriverName"));
                    driver.setPhoneNumber(rs.getString("PhoneNumber"));
                    driver.setEmail(rs.getString("Email"));
                    driver.setGender(rs.getString("Gender"));
                    driver.setDriverLicenseNumber(rs.getString("DriverLicenseNumber"));
                    driver.setDriverLicenseExpirationDate(rs.getDate("DriverLicenseExpirationDate"));
                    driver.setDriverLicenseImage(rs.getString("DriverLicenseImage"));
                    driver.setRemarks(rs.getString("Remarks"));
                    driver.setPassword(rs.getString("password")); // 获取密码
                    return driver;
                }
            }
        }
        return null;
    }

    // 添加验证司机登录的方法
    public Drivers validateDriverLogin(String username, String password) {
        String sql = "SELECT * FROM drivers WHERE DriverID = ? AND password = ?";
        
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            stmt.setInt(1, Integer.parseInt(username));  // 将字符串转换为整数
            stmt.setString(2, password);
            
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return extractDriverFromResultSet(rs);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    // 新增方法：根据司机ID更新密码
    public boolean updatePassword(int driverId, String newPassword) throws SQLException {
        String sql = "UPDATE drivers SET password = ? WHERE DriverID = ?";
        try (Connection conn = connection.getConnection();
             PreparedStatement statement = conn.prepareStatement(sql)) {
            statement.setString(1, newPassword);
            statement.setInt(2, driverId);
            return statement.executeUpdate() > 0;
        }
    }

    // 获取所有可用司机
    public List<Drivers> getAvailableDrivers() throws SQLException {
        List<Drivers> drivers = new ArrayList<>();
        // 确保这里的status状态与数据库中"空闲"匹配
        String sql = "SELECT * FROM drivers WHERE status = '空闲'";

        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {

            while (rs.next()) {
                Drivers driver = extractDriverFromResultSet(rs);
                drivers.add(driver);
            }
        }
        return drivers;
    }

}